﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using AutoMapper;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using PeiYue.Common.Helper;
using PeiYue.Common.HttpContextUser;
using PeiYue.IServices.Common;
using PeiYue.Model;
using PeiYue.Model.Dto.Common;
using PeiYue.Model.Models.Common;

namespace PeiYue.Api.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    // [Authorize(Permissions.Name)]
    public class UploadController : ControllerBase
    {
        IUser _user;
        readonly IMapper _mapper;
        readonly IImgResDaoServices _imgResServices;
        public UploadController(IImgResDaoServices imgResServices, IUser user, IMapper mapper)
        {
            _imgResServices = imgResServices;
            _user = user;
            this._mapper = mapper;
        }

        /// <summary>
        /// 上传文件
        /// </summary>
        /// <param name="file">文件</param>
        /// <param name="json">文件对象</param>
        /// <returns></returns>
        [HttpPost]
        public async Task<MessageModel<ImgResDto>> UploadImage(IFormFile file, [FromForm] string json)
        {
            //Request.Query("");
            ImgResDto dto = Newtonsoft.Json.JsonConvert.DeserializeObject<ImgResDto>(json);
            //ImgResDto dto = new ImgResDto();
            dto.ImgType = 10;
            dto.create_user = _user.ID;
            dto.update_user = _user.ID;
            var model = _mapper.Map<ImgResDao>(dto);
            var res = await _imgResServices.UploadFile(file, model);

            return new MessageModel<ImgResDto>()
            { msg = res.id > 0 ? "成功" : "失败", success = res.id > 0, response = _mapper.Map<ImgResDto>(res) };
        }

        /// <summary>
        /// 从WebAPI下载文件
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [AllowAnonymous]
        public async Task<FileResult> DownLoadOutOfStockOrder()
        {
            //var loginname = Request["loginName"].AsStringWhiteSpace();
            //var mobile = Request["Mobile"].AsStringWhiteSpace();
            try
            {
                string sql = @" Select   维护销售 销售,客户编号, 客户,品名,规格,数量 订单数,发货数,缺货数量 ,'' 原因 from [供应链].[dbo].[v销售订单列表缺货] where   数量>0 and  缺货数量/数量*100>=15  AND  发货日期=@Date order by 维护销售";//@Date 存货编码 产品编号,

                //查询数据库，返回一个DataTable
                DataTable datatabla = await _imgResServices.GetDataTableAsync(sql, new { DateTime.Now.Date });           //RecordsBll.DownLoadRecords(loginname, mobile);
                var fileName = "缺货列表" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx";
                if (datatabla != null)
                {
                    //文件名
                    //if (datatabla.Rows.Count > 50000)
                    //    fileName = "缺货列表" + DateTime.Now.ToString("yyyyMMddHHmm") + ".csv";
                    FileInfo tmpFile = new FileInfo(Directory.GetCurrentDirectory() + @"\\UploadFile\\Tmp\\DownLoadOutOfStockOrder.xlsx");
                    var getbuffer = ExcelHelper.ExportDataTableToExcel(tmpFile, datatabla, true);
                    return File(getbuffer, "application/ms-excel", fileName);
                }
                return File(new byte[0], "application/ms-excel", fileName);
            }
            catch (Exception ex)
            {
                return File(new byte[0], "application/ms-excel", DateTime.Now.Ticks.ToString() + ".xlsx");
            }
        }


        /// <summary>
        /// 从WebAPI下载文件
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [AllowAnonymous]
        public async Task<FileResult> DownLoadCurrSalesOrder(string date)
        {
            //var loginname = Request["loginName"].AsStringWhiteSpace();
            //var mobile = Request["Mobile"].AsStringWhiteSpace();='2020-11-04' 
            try
            {
                string sql = @" 
select 客户编号,客户,发货日期,单号, 发货金额,总金额,null 确认金额 from (
 select 客户ID,客户编号,客户,单号,发货日期, 发货金额,null 总金额,1 ord from [供应链].[dbo].[v销售订单表头]   
 where  CONVERT(varchar(10),发货日期,120)=@date and  客户 like '胡子大厨%'
  union all
 select 客户ID,''客户编号  ,''客户  ,'汇总'单号, null 发货日期, null 发货金额, SUM(发货金额) 总金额,2 ord from [供应链].[dbo].[v销售订单表头]   
 where  CONVERT(varchar(10),发货日期,120)=@date and  客户 like '胡子大厨%'
 GROUP BY 客户ID,客户编号,客户, CONVERT(varchar(10),发货日期,120)
   union all
 select 999999999 客户ID,null 客户编号,null 客户,'总计' 单号,null 发货日期,null  发货金额,SUM(发货金额) 总金额 ,3 ord from [供应链].[dbo].[v销售订单表头]   
 where  CONVERT(varchar(10),发货日期,120)=@date and  客户 like '胡子大厨%'
 ) aa
  order by aa.客户ID,aa.ord ";
                DateTime dt = DateTime.Now;

                if (!string.IsNullOrEmpty(date)) DateTime.TryParse(date, out dt);

                //查询数据库，返回一个DataTable
                DataTable datatabla = await _imgResServices.GetDataTableAsync(sql, new { date = dt.ToString("yyyy-MM-dd") });           //RecordsBll.DownLoadRecords(loginname, mobile);
                var fileName = "胡子大厨订单" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx";
                if (datatabla != null)
                {
                    //文件名
                    //if (datatabla.Rows.Count > 50000)
                    //    fileName = "缺货列表" + DateTime.Now.ToString("yyyyMMddHHmm") + ".csv";
                    FileInfo tmpFile = new FileInfo(Directory.GetCurrentDirectory() + @"\\UploadFile\\Tmp\\DownLoadCurrSalesOrder.xlsx");
                    var getbuffer = ExcelHelper.ExportDataTableToExcel(tmpFile, datatabla, true);
                    return File(getbuffer, "application/ms-excel", fileName);
                }
                return File(new byte[0], "application/ms-excel", fileName);
            }
            catch (Exception ex)
            {
                return File(new byte[0], "application/ms-excel", DateTime.Now.Ticks.ToString() + ".xlsx");
            }
        }

        /// <summary>
        /// 从WebAPI下载文件
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [AllowAnonymous]
        public async Task<FileResult> DownLoadHrRepport(string date)
        {
            //var loginname = Request["loginName"].AsStringWhiteSpace();
            //var mobile = Request["Mobile"].AsStringWhiteSpace();='2020-11-04' 
            try
            {
                string sql = @"  
select lo.制单日期 拣选时间, 分拣等级 供应商等级,CASE WHEN ss.辅计量='斤' AND lo.数量<15 THEN 'F' WHEN ss.辅计量='斤' AND lo.数量<30  THEN 'E' WHEN ss.辅计量='斤' AND lo.数量>=30  THEN 'D' ELSE '' END '重量类型',lo.数量 '分拣数量',
lo.条码,re.namec '收货人',de.namec '投递人', pro.分拣方式,
 ch.[供应商编号] ,ch.[供应商] ,
ss.*   
FROM [供应链].[dbo].[v仓库拣选日志] lo 
left join (select * from [供应链].[dbo].采购订单表体 WITH (Nolock) where 状态 = 1 ) cl on lo.[销售ID]=cl.销售ID
LEFT JOIN [供应链].[dbo].[v采购订单表头] ch  ON cl.单号 = ch.单号  
LEFT JOIN [供应链].[dbo].销售订单表体 ss WITH (Nolock) ON lo.销售ID = ss.fID   
LEFT JOIN [供应链].[dbo].v存货档案 pro    ON ss.存货ID = pro.fID 
left join  (select [BarCode],ru.namec from [oms].[dbo].[oms_srm_receipt_record] rrrd  
left join [oms].[dbo].[oms_ur_user] ru on ru.[id]=UpdateUser
 where rrrd.Status=1)
 re on lo.条码=re.[BarCode]
left join(select [BarCode],ru.namec from  [oms].[dbo].DeliveryRecord dddd  
left join [oms].[dbo].[oms_ur_user] ru on ru.[id]=UpdateUser
 where dddd.Status=0) de on lo.条码=de.[BarCode] 
where   lo.制单日期<@end and  lo.制单日期>@begin and lo.销售ID>0";
                DateTime dt = DateTime.Parse(DateTime.Now.ToShortDateString());
                //'2021-1-9 12:00:16.800''2021-1-8 20:00:16.800'
                if (!string.IsNullOrEmpty(date)) DateTime.TryParse(date, out dt);

                //查询数据库，返回一个DataTable
                DataTable datatabla = await _imgResServices.GetDataTableAsync(sql, new { begin = dt.AddHours(-4).ToString("yyyy-MM-dd HH:mm"), end = dt.AddHours(12).ToString("yyyy-MM-dd HH:mm") });           //RecordsBll.DownLoadRecords(loginname, mobile);
                var fileName = "HrRepport" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx";
                if (datatabla != null)
                {
                    //文件名
                    //if (datatabla.Rows.Count > 50000)
                    //    fileName = "缺货列表" + DateTime.Now.ToString("yyyyMMddHHmm") + ".csv";
                    FileInfo tmpFile = new FileInfo(Directory.GetCurrentDirectory() + @"\\UploadFile\\Tmp\\HrRepport.xlsx");
                    var getbuffer = ExcelHelper.ExportDataTableToExcel(tmpFile, datatabla, true);
                    return File(getbuffer, "application/ms-excel", fileName);
                }
                return File(new byte[0], "application/ms-excel", fileName);
            }
            catch (Exception ex)
            {
                return File(new byte[0], "application/ms-excel", DateTime.Now.Ticks.ToString() + ".xlsx");
            }
        }


        /// <summary>
        /// 扫码装车记录
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [AllowAnonymous]
        public async Task<FileResult> ScanLoadingRecord(string beginDate,string endDate)
        {
           
            try
            {
                string sql = @"  
 select b.存货编码,b.品名,b.规格,b.计量单位,b.数量 as 订单数量,isnull(j.数量,0) as 扫码数量
,isnull(cast((select max(j.包裹号) from 供应链.dbo.v仓库拣选日志(nolock) j where j.销售ID=b.fiD) as varchar(10))+'-'+ cast(j.包裹号 as varchar(10)),'') as 包裹数
,isnull(d.names,'') as 司机,h.制单日期 as 单据日期,isnull(convert(varchar(20),s.create_time,120),'') as 扫码时间,h.客户编号,isnull(g.编号,'') as 供应商编号,h.单号 as 销售订单号,isnull(ph.类型,'') as 采购类型,c.分拣方式
,(case when s.type=1 then '扫码验货' when s.type=2 then '手动验货' else '' end) as 扫码方式
,isnull(convert(varchar(20),h.账单打印,120),'') as 账单打印时间	 from  供应链.dbo.v销售订单表头 (nolock) h
left join 供应链.dbo.销售订单表体 (nolock) b on b.单号=h.单号 and  b.状态=1 
left Join 供应链.dbo.采购订单表体(nolock)  pb on   pb.销售ID=b.fID and pb.状态=1
Left Join 供应链.dbo.采购订单表头(nolock) ph on ph.单号 = pb.单号 and ph.状态>=1 
Left Join 供应链.dbo.v供应商  (nolock)g on ph.供应商ID = g.fID
left Join 供应链.dbo.v存货档案 (nolock)  c on c.fID =b.存货ID 
left Join 供应链.dbo.v仓库拣选日志(nolock)  j on b.fID = j.销售ID 
left join oms.dbo.oms_driver_scanlog s on  s.BarCode=j.条码 and s.status=1
left join oms.dbo.oms_wms_driver d on d.id=s.create_user
Where  h.预计发货>=@beginDate and h.预计发货<@endDate
and h.类型 in('正常订单','调料订单') and c.是否附随品!='是'  ";
                //查询数据库，返回一个DataTable
                DataTable datatabla = await _imgResServices.GetDataTableAsync(sql, new { beginDate =DateTime.Now.Date, endDate = DateTime.Now.AddDays(1).Date });          
                var fileName = "扫码装车" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
                if (datatabla != null)
                {
                    //文件名
                    //if (datatabla.Rows.Count > 50000)
                    //    fileName = "缺货列表" + DateTime.Now.ToString("yyyyMMddHHmm") + ".csv";
                    FileInfo tmpFile = new FileInfo(Directory.GetCurrentDirectory() + @"\\UploadFile\\Tmp\\ScanLoading.xlsx");
                    var getbuffer = ExcelHelper.ExportDataTableToExcel(tmpFile, datatabla, true);
                    return File(getbuffer, "application/ms-excel", fileName);
                }
                return File(new byte[0], "application/ms-excel", fileName);
            }
            catch (Exception ex)
            {
                return File(new byte[0], "application/ms-excel", DateTime.Now.Ticks.ToString() + ".xlsx");
            }
        }

        /// <summary>
        /// 扫码收货记录
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [AllowAnonymous]
        public async Task<FileResult> ScanReceivedRecord(string beginDate, string endDate)
        {

            try
            {
                string sql = @"  
  select b.存货编码,b.品名,b.规格,b.计量单位,b.数量 as 订单数量,isnull(j.数量,0) as 拣选数量
,isnull(cast((select max(j.包裹号) from 供应链.dbo.v仓库拣选日志(nolock) j where j.销售ID=b.fiD) as varchar(10))+'-'+ cast(j.包裹号 as varchar(10)),'') as 包裹数
,isnull(d.namec,'') as 收货人,h.制单日期 as 单据日期,isnull(convert(varchar(20),s.UpdateTime,120),'') as 扫码时间,h.客户编号,isnull(g.编号,'') as 供应商编号,h.单号 as 销售订单号,isnull(ph.类型,'') as 采购类型  from  供应链.dbo.v销售订单表头 (nolock) h
left join 供应链.dbo.销售订单表体 (nolock) b on b.单号=h.单号  and  b.状态=1  
left Join 供应链.dbo.采购订单表体(nolock)  pb on   pb.销售ID=b.fID and pb.状态=1
Left Join 供应链.dbo.采购订单表头(nolock) ph on ph.单号 = pb.单号 and ph.状态>=1 
Left Join 供应链.dbo.v供应商  (nolock)g on ph.供应商ID = g.fID
left Join 供应链.dbo.v存货档案 (nolock)  c on c.fID =b.存货ID 
left Join 供应链.dbo.v仓库拣选日志(nolock)  j on b.fID = j.销售ID 
left join oms.dbo.oms_srm_receipt_record s on  s.BarCode=j.条码 and s.Status=1  and s.receiptQty>0
left join oms.dbo.oms_ur_user d on d.id=s.UpdateUser
Where  h.预计发货>=@beginDate and h.预计发货<@endDate
and h.类型 in('正常订单','调料订单') and c.是否附随品!='是'  ";
                //查询数据库，返回一个DataTable
                DataTable datatabla = await _imgResServices.GetDataTableAsync(sql, new { beginDate = DateTime.Now.Date, endDate = DateTime.Now.AddDays(1).Date });
                var fileName = "扫码收货" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
                if (datatabla != null)
                {
                    //文件名
                    //if (datatabla.Rows.Count > 50000)
                    //    fileName = "缺货列表" + DateTime.Now.ToString("yyyyMMddHHmm") + ".csv";
                    FileInfo tmpFile = new FileInfo(Directory.GetCurrentDirectory() + @"\\UploadFile\\Tmp\\ScanReceived.xlsx");
                    var getbuffer = ExcelHelper.ExportDataTableToExcel(tmpFile, datatabla, true);
                    return File(getbuffer, "application/ms-excel", fileName);
                }
                return File(new byte[0], "application/ms-excel", fileName);
            }
            catch (Exception ex)
            {
                return File(new byte[0], "application/ms-excel", DateTime.Now.Ticks.ToString() + ".xlsx");
            }
        }
        /// <summary>
        /// 扫码投递记录
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [AllowAnonymous]
        public async Task<FileResult> ScanDeliveryRecord(string beginDate, string endDate)
        {
            try
            {
                string sql = @"  
  select b.存货编码,b.品名,b.规格,b.计量单位,b.数量 as 订单数量,isnull(j.数量,0) as 拣选数量
,isnull(cast((select max(j.包裹号) from 供应链.dbo.v仓库拣选日志(nolock) j where j.销售ID=b.fiD) as varchar(10))+'-'+ cast(j.包裹号 as varchar(10)),'') as 包裹数
,isnull(d.namec,'') as 收货人,h.制单日期 as 单据日期,isnull(convert(varchar(20),s.UpdateTime,120),'') as 扫码时间,h.客户编号,isnull(g.编号,'') as 供应商编号,h.单号 as 销售订单号,isnull(ph.类型,'') as 采购类型  from  供应链.dbo.v销售订单表头 (nolock) h
left join 供应链.dbo.销售订单表体 (nolock) b on b.单号=h.单号  and  b.状态=1  
left Join 供应链.dbo.采购订单表体(nolock)  pb on   pb.销售ID=b.fID and pb.状态=1
Left Join 供应链.dbo.采购订单表头(nolock) ph on ph.单号 = pb.单号 and ph.状态>=1 
Left Join 供应链.dbo.v供应商  (nolock)g on ph.供应商ID = g.fID
left Join 供应链.dbo.v存货档案 (nolock)  c on c.fID =b.存货ID 
left Join 供应链.dbo.v仓库拣选日志(nolock)  j on b.fID = j.销售ID 
left join oms.dbo.oms_srm_sorting_record s on  (cast(s.OrderDetailId as varchar(10))+'G'+cast(PackNum as varchar(10)))=j.条码  and s.Status=1 and s.Qty>0
left join oms.dbo.oms_ur_user d on d.id=s.CreateUser
Where  h.预计发货>=@beginDate and h.预计发货<@endDate
and h.类型 in('正常订单','调料订单') and c.是否附随品!='是'  ";
                //查询数据库，返回一个DataTable
                DataTable datatabla = await _imgResServices.GetDataTableAsync(sql, new { beginDate = DateTime.Now.Date, endDate = DateTime.Now.AddDays(1).Date });
                var fileName = "扫码投递" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
                if (datatabla != null)
                {
                    //文件名
                    //if (datatabla.Rows.Count > 50000)
                    //    fileName = "缺货列表" + DateTime.Now.ToString("yyyyMMddHHmm") + ".csv";
                    FileInfo tmpFile = new FileInfo(Directory.GetCurrentDirectory() + @"\\UploadFile\\Tmp\\ScanDelivery.xlsx");
                    var getbuffer = ExcelHelper.ExportDataTableToExcel(tmpFile, datatabla, true);
                    return File(getbuffer, "application/ms-excel", fileName);
                }
                return File(new byte[0], "application/ms-excel", fileName);
            }
            catch (Exception ex)
            {
                return File(new byte[0], "application/ms-excel", DateTime.Now.Ticks.ToString() + ".xlsx");
            }
        }
    }
}
